﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public class SysMenuItemListDAL
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();
        public DataSet LoadMenuByUsername(int username, string system)
        {        
            DataSet ds = new DataSet();
            dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@username", username);
                dbManager.AddParameters(2, "@system", system);

                string sql = "SELECT DISTINCT ml.MENU_ITEM_SEQ"
                            + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS MENU_ITEM_NAME"
                            + ", ml.UPPER_MENU_ITEM_SEQ"
                            + ", ml.MENU_ITEM_PATH"
                            + ", ml.MENU_ITEM_TARGET"
                            + ", ml.RECORD_STATUS"
                            + ", r.system"
                        + " FROM SYS_SC_USERS u"
                        + " INNER JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                            + " INNER JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = r.ROLE_SEQ"
                            + " INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ"
                            + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                        + " WHERE u.RECORD_STATUS <> @delete_status"
                            + " AND ur.RECORD_STATUS <> @delete_status"
                            + " AND r.RECORD_STATUS <> @delete_status"
                            + " AND p.RECORD_STATUS <> @delete_status"
                            + " AND ml.RECORD_STATUS <> @delete_status"
                            + " AND u.USER_SEQ = @username"
                            + " AND r.system = @system"
                        + " ORDER BY ml.MENU_ITEM_CODE + 1";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
    }
}
